package com.xqgl.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.xqgl.bean.User;
import com.xqgl.dao.UserDao;
import com.xqgl.util.ConnertionUtil;

public class UserDaoImp implements UserDao{
	Connection conn = ConnertionUtil.getConnection();
	PreparedStatement ps;
	Statement statement;
	
	public ArrayList<User> getAllUserInfos() {
		ArrayList<User> users = new ArrayList<>();
		String sql = "select * from user ";
		try {
			ps = conn.prepareStatement(sql);
			ResultSet rst=ps.executeQuery(sql);
			rst.last();
			rst.beforeFirst();
			while(rst.next()) {
				User user = new User();
				user.setId(rst.getInt("id"));
				user.setVillagename(rst.getString("villagename"));
				user.setUserhpro(rst.getString("userhpro"));
				user.setUsername(rst.getString("username"));
				user.setUserpic(rst.getString("userpic"));
				user.setUserid(rst.getInt("userid"));
				user.setUserphone(rst.getInt("userphone"));
				user.setUseroccupation(rst.getString("useroccupation"));
				user.setUserbirth(rst.getString("userbirth"));
				user.setSex(rst.getString("sex"));
				user.setType(rst.getString("type"));
				user.setRemark(rst.getString("remark"));
				user.setUserctime(rst.getString("userctime"));
				users.add(user);
			}		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return users;
	}
	
	@Override
	public int add(User user) throws SQLException {
		String sql = "insert into user(villagename,userhpro,username,userpic,userid,userphone,useroccupation,userbirth,sex,type,remark,userctime)"
				+ "values(?,?,?,?,?,?,?,?,?,?,?,?)";
		ps = conn.prepareStatement(sql);
		ps.setString(1, user.getVillagename());
		ps.setString(2, user.getUserhpro());
		ps.setString(3, user.getUsername());
		ps.setString(4, user.getUserpic());
		ps.setInt(5, user.getUserid());
		ps.setInt(6, user.getUserphone());
		ps.setString(7, user.getUseroccupation());
		ps.setString(8, user.getUserbirth());
		ps.setString(9, user.getSex());
		ps.setString(10, user.getType());
		ps.setString(11, user.getRemark());
		ps.setString(12, user.getUserctime());
		int a = ps.executeUpdate();
		ps.close();
		return a;
	}

	@Override
	public int update(User user) throws SQLException {
		String sql = "update user set villagename = '"+user.getVillagename()+"', userhpro = '"+user.getUserhpro()+"', username = '"+user.getUsername()+"', userpic = '"+user.getUserpic()+"', userid = "+user.getUserid()+","
				+ "userphone = "+user.getUserphone()+", useroccupation = '"+user.getUseroccupation()+"', userbirth = '"+user.getUserbirth()+"', sex = '"+user.getSex()+"',"
						+ "type = '"+user.getType()+"', remark = '"+user.getRemark()+"', userctime = '"+user.getUserctime()+"' where id = "+user.getId()+"";
		ps = conn.prepareStatement(sql);
		int b = ps.executeUpdate();
		ps.close();
		return b;
	}

	@Override
	public boolean delete(int id) throws SQLException {
		String sql = "delete from user where id = "+id+"";
		statement = conn.createStatement();
		boolean c = statement.execute(sql);
		statement.close();
		return !c;
	}

	@Override
	public User findById(int id) throws SQLException {
		User user = new User();
		String sql = "select * from user where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ResultSet set = ps.executeQuery();
			while(set.next()){
				user.setId(set.getInt("id"));
				user.setVillagename(set.getString("villagename"));
				user.setUserhpro(set.getString("userhpro"));
				user.setUsername(set.getString("username"));
				user.setUserpic(set.getString("userpic"));
				user.setUserid(set.getInt("userid"));
				user.setUserphone(set.getInt("userphone"));
				user.setUseroccupation(set.getString("useroccupation"));
				user.setUserbirth(set.getString("userbirth"));
				user.setSex(set.getString("sex"));
				user.setType(set.getString("type"));
				user.setRemark(set.getString("remark"));
				user.setUserctime(set.getString("userctime"));
			}
			ps.close();
			return user;
	}

}
